--------------------------------------------------------------------------------
-- @name: add partition plan change
-- @author: dion cho
-- @note: test case for the plan change by partition addition
--------------------------------------------------------------------------------

set pages 100
set lines 150
set trimspool on
set timing off

-- create partitioned tables
drop table t1 purge;

create table t1(c1 number, c2 number, c3 number)
partition by list(c1) (
	partition p1 values (1),
	partition p2 values (2),
	partition p3 values (3),
	partition p4 values (4)
);

insert into t1 
select mod(level, 4)+1, mod(level, 10000)+1, level
from dual
connect by level <= 40000;

create index t1_n1 on t1(c1, c2) local;

exec dbms_stats.gather_table_stats(user, 't1', granularity=>'global and partition');

-- plan use index?
var b1 number;
var b2 number;

exec :b1 := 1; :b2 := 1;

select /*+ gather_plan_statistics */
	count(c3)
from t1
where c1 = :b1 and c2 = :b2;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- add new partition and insert small number of data
alter table t1 drop partition p5;

alter table t1 add partition p5 values(5);

insert into t1
select 5, 1, level
from dual
connect by level <= 1000;

exec dbms_stats.gather_table_stats(user, 't1', granularity=>'global and partition');

alter system flush shared_pool;

-- plan use fts?
exec :b1 := 5; :b2 := 1;

select /*+ gather_plan_statistics */
	count(c3)
from t1
where c1 = :b1 and c2 = :b2;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- but fts is inefficient with this value
exec :b1 := 1; :b2 := 1;

select /*+ gather_plan_statistics */
	count(c3)
from t1
where c1 = :b1 and c2 = :b2;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


-- method? 
-- 1. disable bind peeking
alter session set "_optim_peek_user_binds" = false;

alter system flush shared_pool;

exec :b1 := 5; :b2 := 1;

select /*+ gather_plan_statistics */
	count(c3)
from t1
where c1 = :b1 and c2 = :b2;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

exec :b1 := 1; :b2 := 1;

select /*+ gather_plan_statistics */
	count(c3)
from t1
where c1 = :b1 and c2 = :b2;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


-- 2. copy table stats
exec dbms_stats.copy_table_stats(user, 't1', 'p1', 'p5');

@part_stat t1 p1
@part_stat t1 p5

alter session set "_optim_peek_user_binds" = true;

alter system flush shared_pool;

exec :b1 := 5; :b2 := 1;

select /*+ gather_plan_statistics */
	count(c3)
from t1
where c1 = :b1 and c2 = :b2;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


set timing on
